﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace H2.NET.Web.API.Common
{

    public class DBHelper : IDisposable
    {

        private SqlConnection sqlCon;
        /// <summary>
        /// 连接对象
        /// </summary>
        public SqlConnection SqlCon
        {
            get
            {
                if (sqlCon.State == ConnectionState.Closed)
                {
                    sqlCon.Open();
                }
                return sqlCon;
            }
        }

        public static DBHelper Instance { get; set; }
        static DBHelper()
        {
            Instance = new DBHelper();
        }
        public DBHelper()
        {
            string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["AkzoNobelSMDSync"].ConnectionString;
            
            if (string.IsNullOrEmpty(connStr))
            {
                connStr = "Data Source=.;Initial Catalog=AkzoNobelSMD;User ID=sa;Password=1qaz2wsx3edc$";
            }
            sqlCon = new SqlConnection(connStr);
        }
        public DBHelper(string connStr)
        {
            sqlCon = new SqlConnection(connStr);
        }
        /// <summary>
        /// 执行查询的方法
        /// </summary>
        /// <param name="strSql">T-SQL语句</param>
        /// <param name="sqlPar">参数数组</param>
        /// <returns>返回一个DataReader对象</returns>
        public SqlDataReader ExecuteReader(string strSql, SqlParameter[] sqlPar, CommandType type)
        {
            using (SqlCommand sqlCommand = new SqlCommand(strSql, SqlCon))
            {
                sqlCommand.CommandType = type;
                sqlCommand.CommandTimeout = 300;
                if (sqlPar != null)
                {
                    sqlCommand.Parameters.AddRange(sqlPar);
                }
                return sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }
        /// <summary>
        ///  增删改查
        /// </summary>
        /// <param name="strSql">TSQl / Proc</param>
        /// <param name="sqlPar"></param>
        /// <param name="type">Text / Proc</param>
        /// <returns></returns>
        public int Execute(string strSql, SqlParameter[] sqlPar, CommandType type)
        {
            using (SqlCommand sqlCommand = new SqlCommand(strSql, SqlCon))
            {
                sqlCommand.CommandType = type;
                sqlCommand.CommandTimeout = 300;
                if (sqlPar != null)
                {
                    sqlCommand.Parameters.AddRange(sqlPar);
                }
                int cou = sqlCommand.ExecuteNonQuery();
                sqlCon.Close();
                return cou;
            }
        }
        /// <summary>
        /// 执行查询的方法
        /// </summary>
        /// <param name="strcon">连接字符串</param>
        /// <param name="cmdText">sql语句</param>
        /// <returns>返回DataSet</returns>
        public DataSet ExcuteQuery(string cmdText, SqlParameter[] sqlpar, CommandType type)
        {
            try
            {
                using (SqlCommand sqlcom = new SqlCommand(cmdText, SqlCon))
                {
                    sqlcom.CommandType = type;
                    sqlcom.CommandTimeout = 300;
                    if (sqlpar != null)
                    {
                        sqlcom.Parameters.AddRange(sqlpar);
                    }
                    SqlDataAdapter sqlda = new SqlDataAdapter(sqlcom);
                    DataSet ds = new DataSet();
                    sqlda.Fill(ds, "table");
                    sqlCon.Close();
                    return ds;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
            return null;
        }

        /// <summary>
        /// 查询第一行第一列的数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sqlpar"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public object ExcuteMyScalar(string sql, SqlParameter[] sqlpar, CommandType type)
        {
            using (SqlCommand sqlCommand = new SqlCommand(sql, SqlCon))
            {
                sqlCommand.CommandType = type;
                sqlCommand.CommandTimeout = 300;
                if (sqlpar != null)
                {
                    sqlCommand.Parameters.AddRange(sqlpar);
                }
                return sqlCommand.ExecuteScalar();
            }
        }


        public void Dispose()
        {
            if (sqlCon.State != ConnectionState.Closed)
            {
                sqlCon.Close();
            }
            sqlCon.Dispose();
        }


        public bool BatchInsert(string sql, SqlParameter[] sqlpar, DataTable dt)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            da.InsertCommand = new SqlCommand();
            da.InsertCommand.CommandText = sql;
            da.InsertCommand.Connection = sqlCon;
            da.InsertCommand.Parameters.AddRange(sqlpar);
            int cou = da.Update(dt);
            if (cou > 0)
            {
                return true;
            }
            return false;
        }


        public bool BatchUpdate(string sql, SqlParameter[] sqlpar, DataTable dt)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            da.UpdateCommand = new SqlCommand();
            da.UpdateCommand.CommandText = sql;
            da.UpdateCommand.Connection = sqlCon;
            da.UpdateCommand.Parameters.AddRange(sqlpar);
            da.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;

            int cou = da.Update(dt);
            if (cou > 0)
            {
                return true;
            }
            return false;
        }


        public bool BatchInsertDt(DataTable dt)
        {
            DataColumnCollection columnColl = dt.Columns;
            string strColumns = string.Empty;
            string strValueParams = string.Empty;

            List<SqlParameter> paramList = new List<SqlParameter>();
            foreach(DataColumn column in columnColl)
            {
                 strColumns+=string.Format("{0},",column.ColumnName);
                 strValueParams +=string.Format("@{0},",column.ColumnName);

                 SqlParameter sp = GetParam(column);
                 
                 paramList.Add(sp);

            }
            strColumns = strColumns.Substring(0,strColumns.Length-1);

            string sql = string.Format("INSERT INTO [dbo].{0}({1}) VALUES ({2})", dt.TableName, strColumns, strValueParams);

            SqlDataAdapter da = new SqlDataAdapter();
            da.InsertCommand = new SqlCommand();
            da.InsertCommand.CommandText = sql;
            da.InsertCommand.Connection = sqlCon;
            da.InsertCommand.Parameters.AddRange(paramList.ToArray());
            int cou = da.Update(dt);
            if (cou > 0)
            {
                return true;
            }
            return false;


        }


        private SqlParameter GetParam(DataColumn column)
        {
            Type type = column.DataType;
            string columnName = column.ColumnName;
            int length = column.MaxLength;
            if (type == Type.GetType("System.Guid"))
            {
                SqlParameter sp = new SqlParameter("@" + columnName, SqlDbType.UniqueIdentifier);
                sp.SourceVersion = DataRowVersion.Current;
                sp.SourceColumn = columnName;
                return sp;
            }
            else if (type == Type.GetType("System.String"))
            {
                SqlParameter sp = new SqlParameter("@" + columnName, SqlDbType.VarChar, length);
                sp.SourceVersion = DataRowVersion.Current;
                sp.SourceColumn = columnName;
                return sp;
            }
            else if (type == Type.GetType("System.DateTime"))
            {
                SqlParameter sp = new SqlParameter("@" + columnName, SqlDbType.DateTime);
                sp.SourceVersion = DataRowVersion.Current;
                sp.SourceColumn = columnName;
                return sp;
            }
            else if (type == Type.GetType("System.Int32"))
            {
                SqlParameter sp = new SqlParameter("@" + columnName, SqlDbType.Int);
                sp.SourceVersion = DataRowVersion.Current;
                sp.SourceColumn = columnName;
                return sp;
            }
            else {
                SqlParameter sp = new SqlParameter("@" + columnName, SqlDbType.VarChar);
                sp.SourceVersion = DataRowVersion.Current;
                sp.SourceColumn = columnName;
                return sp;  
            }
        }



    }
}
